var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery,getxl_util_lsh } = require('./server_util.js')

const log = require('../../../logger.js');
const db = require('../../../db/Redisdb.js');
var logger = require('../../../logger.js').logger;

//查询合同模板·列表
router.post('/htmblb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_htmb order by mbbh desc`)
   res_util(res, {rtdata}, true, "查询成功")
})

//查询合同模板·列表通过id
router.post('/query_htmb_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_htmb where id='${req.body.id}' order by mbbh desc`)
   res_util(res, { formdata: data[0]}, true, "查询成功")
})

//新增合同模板·
router.post('/add_htmb', async function (req, res) {
   let { formdata, storage} = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_htmb where mbbh='${formdata.mbbh.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，模板编码已存在")
      return
   }
   //不存在编码插入
  let getnextwlbmsql = await usequery(`select top 1 mbbh from shipSRM_htmb  order by mbbh desc`)
 
  let nextmbbh = getnextwlbmsql.length > 0 ? parseInt(getnextwlbmsql[0].mbbh.slice(-3)) + 1 : 1
  formdata.mbbh.value = formdata.mbbh.value + nextmbbh.toString().padStart(3, '0')


   var insrtobj = await insert_table(formdata, "shipSRM_htmb")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增合同模板·', '新增', 'shipSRM_htmb')
   
   res_util(res, { formdata: insrtobj.data}, true, "新增成功")
})

//更新合同模板·
router.post('/update_htmb', async function (req, res) {
   let { formdata, storage} = req.body
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_htmb")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新合同模板·', '更新', 'shipSRM_htmb')
   formdata = updatobj.data;

   res_util(res, { formdata: updatobj.data}, true, "更新成功")
})

//删除合同模板·
router.post('/delete_htmb', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_htmb where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除合同模板·', '删除', 'shipSRM_htmb')
   res_util(res, {}, true, "删除成功")
})




//查询合同模板·列表
router.post('/xymblb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_xymb order by mbbh desc`)
   res_util(res, {rtdata}, true, "查询成功")
})

//查询合同模板·列表通过id
router.post('/query_xymb_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xymb where id='${req.body.id}' order by mbbh desc`)
   res_util(res, { formdata: data[0]}, true, "查询成功")
})

//新增合同模板·
router.post('/add_xymb', async function (req, res) {
   let { formdata, storage} = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_xymb where mbbh='${formdata.mbbh.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，模板编码已存在")
      return
   }
   //不存在编码插入
  let getnextwlbmsql = await usequery(`select top 1 mbbh from shipSRM_xymb  order by mbbh desc`)
 
  let nextmbbh = getnextwlbmsql.length > 0 ? parseInt(getnextwlbmsql[0].mbbh.slice(-3)) + 1 : 1
  formdata.mbbh.value = formdata.mbbh.value + nextmbbh.toString().padStart(3, '0')


   var insrtobj = await insert_table(formdata, "shipSRM_xymb")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增协议模板·', '新增', 'shipSRM_xymb')
   
   res_util(res, { formdata: insrtobj.data}, true, "新增成功")
})

//更新合同模板·
router.post('/update_xymb', async function (req, res) {
   let { formdata, storage,mxdata,delidarr} = req.body
   var db=new Msdb()
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_xymb where 
   mbbh='${formdata.mbbh.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，模板编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_xymb")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新协议模板·', '更新', 'shipSRM_xymb')
   formdata = updatobj.data;

   res_util(res, { formdata: updatobj.data}, true, "更新成功")
})

//删除合同模板·
router.post('/delete_xymb', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_xymb where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除协议模板·', '删除', 'shipSRM_xymb')
   res_util(res, {}, true, "删除成功")
})



//popview 
router.post('/getpopupview', async function (req, res) {
   var db = new Msdb();
   var viewname = req.body.viewname;
   var checkifviewres = await db.query(`SELECT * FROM sys.views WHERE name = '${viewname}' `)
   if (checkifviewres.recordsets[0].length == 0) {
      res_util(res, [], false, "不是视图查询失败")
   }
   else {
      var rtdata = await db.query(`select  TOP 1000 *  from ${viewname}`)
      res_util(res, rtdata.recordsets[0], true, "查询成功！")
   }

});

//查询销售订单明细通过料号
router.post('/query_crkmx_by_wlbm', async function (req, res) {
   var {selectedRows} = req.body
   var rtdata = await usequery(`select * from v_crkmx_history where wlbm in (${selectedRows.map(item => `'${item.wlbm}'`).join(',')})`)
   res_util(res, rtdata, true, "查询成功！")
});

//查询协议管理
router.post('/query_xygl', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_xyml order by id desc`)
   res_util(res, rtdata, true, "查询成功")
});

//查询协议管理通过id
router.post('/query_xygl_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_xyml where id='${req.body.id}' order by id desc`)
   var lsbbdata = await usequery(`select * from shipSRM_xyml_lsbb where mainid='${req.body.id}' order by id desc`)
   res_util(res, { formdata: data[0],lsbbdata }, true, "查询成功")
});

//新增协议管理
router.post('/add_xygl', async function (req, res) {
   var { formdata, storage } = req.body
   var db = new Msdb()
   var xydmData = await usequery(` select top 1 * from shipSRM_xyml order by xybh desc `)
   var nextxybh = xydmData.length > 0 ? parseInt(xydmData[0].xybh.slice(-3)) + 1 : '10000'
   formdata.xybh.value = 'XY' + nextxybh.toString().padStart(3, '0')

   let mxid = await getxl_util()

   var insrtobj = await insert_table(formdata, "shipSRM_xyml")
   

   await saveczlog_util(insrtobj.insertsql, storage, '', '新增协议管理', '新增', 'shipSRM_xyml')

   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;

   

   let mxsql = `insert into shipSRM_xyml_lsbb (id,mainid,bbh,scfj,bz,fbrq) values ('${mxid}','${mainid}','1.00','${formdata.fj.value}','${formdata.bz.value}',getdate())`
   await db.update(mxsql)
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
});


//修改协议管理
router.post('/edit_xygl', async function (req, res) {
   var { formdata, storage } = req.body
   var updatobj = await update_table_byid(formdata, "shipSRM_xyml")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改协议管理', '修改', 'shipSRM_xyml')
   res_util(res, { formdata: updatobj.data }, true, "修改成功")
});


//更新协议管理
router.post('/update_xygl', async function (req, res) {
   var { formdata, storage } = req.body
   var db = new Msdb()
   let ybbh = formdata.xybb.value
   let xbbh = (parseFloat(formdata.xybb.value) + 0.01).toFixed(2)
   formdata.xybb.value = xbbh
   let bz = formdata.bz.value
   let lsbbfj = formdata.fj.value
   let mxid = await getxl_util()
   let mxsql = `insert into shipSRM_xyml_lsbb (id,mainid,bbh,scfj,bz,fbrq) values ('${mxid}','${formdata.id.value}','${xbbh}','${lsbbfj}','${bz}',getdate())`
   await db.update(mxsql)
   console.log("原版本号为：",ybbh,"新版本号为：",xbbh)
   
   var updatobj = await update_table_byid(formdata, "shipSRM_xyml")

   
   await saveczlog_util(updatobj.updatesql, storage, '', '更新协议管理', '更新', 'shipSRM_xyml')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
});

//禁用协议管理
router.post('/disable_xygl', async function (req, res) {
   var { id, storage } = req.body
   var updatobj = await update_table_byid({ id, ifshow: 0 }, "shipSRM_xyml")
   await saveczlog_util(updatobj.updatesql, storage, '', '禁用协议管理', '禁用', 'shipSRM_xyml')
   res_util(res, {}, true, "禁用成功")
});

//删除协议管理
router.post('/delete_xygl', async function (req, res) {
   var { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_xyml where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除协议管理', '删除', 'shipSRM_xyml')
   res_util(res, {}, true, "删除成功")
});




//查询部门树
router.post('/bmda_tree', async function (req, res) {
   var rtdata = await usequery(`select *,bmmc name,bmbh bm from shipSRM_bmda order by bmbh desc`)
   res_util(res, rtdata, true, "查询成功")
})


//查询物料分类new树
router.post('/wlfllb_new', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_wlfl_new order by flbm desc`)
   res_util(res, rtdata, true, "查询成功")
})



//查询物料分类new列表通过id
router.post('/query_wlfl_byid_new', async function (req, res) {
   var data = await usequery(`select * from shipSRM_wlfl_new where id='${req.body.id}' order by flbm desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})


//查询合同台账byid
router.post('/query_httz_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_httz where id='${req.body.id}' order by id desc`)
   var cpmxdata = await usequery(`select * from shipSRM_httz_cpmx where mainid='${req.body.id}' order by id desc`)
   var rzmxdata = await usequery(`select * from shipSRM_httz_rzmx where mainid='${req.body.id}' order by id desc`)

   res_util(res, { formdata: data[0],cpmxdata,rzmxdata}, true, "查询成功")
   
})


//新增合同台账
router.post('/add_httz', async function (req, res) {
   let { formdata,cpmxdata,storage } = req.body

   let xlm = await getxl_util_lsh("seq_httz",3)
   formdata.htbh.value = "HTTZ-"+xlm

   var insrtobj = await insert_table(formdata, "shipSRM_httz")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增合同台账', '新增', 'shipSRM_httz')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in cpmxdata) {
      let mxitem = cpmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_httz_cpmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增合同明细', '新增', 'shipSRM_httz_cpmx')                     
   }
   

   var rtcpmxdata = await usequery(`select *  from shipSRM_httz_cpmx where mainid ='${formdata.id.value}'`)
   var rtrzmxdata = await usequery(`select *  from shipSRM_httz_rzmx where mainid ='${formdata.id.value}'`)


   res_util(res, { formdata: formdata,cpmxdata:rtcpmxdata,rzmxdata:rtrzmxdata }, true, "新增成功")
})

//httzlb
router.post('/httzlb', async function (req, res) {
   let { ksrq,jsrq,xmmc,htzt } = req.body
   let query_sql = `select * from shipSRM_httz where 1=1`
   if(ksrq){
      query_sql += ` and qsrq >= '${ksrq}'`
   }
   if(jsrq){
      query_sql += ` and qsrq <= '${jsrq}'`
   }
   if(xmmc){
      query_sql += ` and xmmc like '%${xmmc}%'`
   }
   if(htzt){
      query_sql += ` and htzt = '${htzt}'`
   }
   query_sql += ` order by qsrq desc`
   var rtdata = await usequery(query_sql)
   res_util(res, rtdata, true, "查询成功")


})




//更新合同台账
router.post('/update_httz', async function (req, res) {
   let { formdata,cpmxdata,cpmxdelidarr,storage } = req.body
   let db = new Msdb()
   let updatobj = await update_table_byid(formdata, "shipSRM_httz")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新合同台账', '更新', 'shipSRM_httz')
   formdata = updatobj.data;

   var mainid = updatobj.data.id.value;
   
   for (var mxkey in cpmxdata) {
      let mxitem = cpmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_httz_cpmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_httz_cpmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_httz_cpmx")
      }        
   }
   
   //删除delidarr
   await db.update(`delete from shipSRM_httz_cpmx where id in (${cpmxdelidarr.map(item => `'${item}'`).join(',')}) `)
   

   var rtcpmxdata = await usequery(`select *  from shipSRM_httz_cpmx where mainid ='${formdata.id.value}'`)
   var rtrzmxdata = await usequery(`select *  from shipSRM_httz_rzmx where mainid ='${formdata.id.value}'`)


   res_util(res, { formdata: formdata,cpmxdata:rtcpmxdata,rzmxdata:rtrzmxdata }, true, "更新成功")
})




//更新供应商档案
router.post('/update_gys', async function (req, res) {
   let { formdata,ghmxdata,sqmxdata,yhmxdata,zzmxdata,ghdelidarr,sqdelidarr,yhdelidarr,zzdelidarr, storage } = req.body
   // var ifwlbm = await checkbysql(` select top 1 * from shipSRM_gys where 
   // bm='${formdata.bm.value}' and id !='${formdata.id.value}'`)
   // if (ifwlbm) {
   //    res_util(res, { formdata }, false, "更新失败，物料编码已存在")
   //    return
   // }
   //不存在更新
   let db = new Msdb()
   let updatobj = await update_table_byid(formdata, "shipSRM_gys")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新供应商档案', '更新', 'shipSRM_gys')

   var mainid = updatobj.data.id.value;
   for (var mxkey in ghmxdata) {
      let mxitem = ghmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gys_ghmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gys_ghmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gys_ghmx")
      }
   }
   for(var mxkey in sqmxdata){
      let mxitem = sqmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gys_sqmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gys_sqmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gys_sqmx")
      }
   }
   for(var mxkey in yhmxdata){
      let mxitem = yhmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gys_yhmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gys_yhmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gys_yhmx")
      }
   }
   for(var mxkey in zzmxdata){
      let mxitem = zzmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gys_zsmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gys_zsmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gys_zsmx")
      }
   }
   
   //删除delidarr
   await db.update(`delete from shipSRM_gys_ghmx where id in (${ghdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_gys_sqmx where id in (${sqdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_gys_yhmx where id in (${yhdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from shipSRM_gys_zsmx where id in (${zzdelidarr.map(item => `'${item}'`).join(',')}) `)

   var rtghmxdata = await usequery(`select *  from shipSRM_gys_ghmx where mainid ='${formdata.id.value}'`)
   var rtsqmxdata = await usequery(`select *  from shipSRM_gys_sqmx where mainid ='${formdata.id.value}'`)
   var rtyhmxdata = await usequery(`select *  from shipSRM_gys_yhmx where mainid ='${formdata.id.value}'`)
   var rtzzmxdata = await usequery(`select *  from shipSRM_gys_zsmx where mainid ='${formdata.id.value}'`)
   var rtkpmxdata = await usequery(`select *  from shipSRM_gys_kpmx where mainid ='${formdata.id.value}'`)

   
   res_util(res, { formdata: updatobj.data,ghmxdata:rtghmxdata,sqmxdata:rtsqmxdata,yhmxdata:rtyhmxdata,zzmxdata:rtzzmxdata,kpmxdata:rtkpmxdata }, true, "更新成功")
})

//删除供应商档案
router.post('/delete_gys', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_gys where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除供应商档案', '删除', 'shipSRM_wlda')
   res_util(res, {}, true, "删除成功")
})





//查询人员档案列表
router.post('/rylb', async function (req, res) {
   var rtdata = await usequery(`select *,
      ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) as bmmc_row,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) = 1 
         THEN bmmc ELSE NULL END AS bmmcq,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) = 1 
         THEN gw ELSE NULL END AS gwq
      from shipSRM_ryda where ryzt='${req.body.type}' order by bmmc,gw desc `)
   res_util(res, rtdata, true, "查询成功")
})

//查询人员档案列表通过id
router.post('/query_ry_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_ryda where id='${req.body.id}' order by rzsj desc`)
   res_util(res, {formdata:data[0]}, true, "查询成功")
})

//新增人员档案
router.post('/add_ry', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_ryda where gh='${formdata.gh.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，工号已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_ryda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增人员档案', '新增', 'shipSRM_ryda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新人员档案
router.post('/update_ry', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_ryda where 
   zh='${formdata.zh.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，账号已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_ryda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新人员档案', '更新', 'shipSRM_ryda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})


//删除人员档案
router.post('/delete_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_ryda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除人员档案', '删除', 'shipSRM_ryda')
   res_util(res, {}, true, "删除成功")
})

//禁用人员档案
router.post('/disable_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `update shipSRM_ryda set ryzt='禁用',lssj=getdate() where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '禁用人员档案', '修改', 'shipSRM_ryda')
   res_util(res, {}, true, "禁用成功")
})

//启用人员档案
router.post('/able_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `update shipSRM_ryda set ryzt='正常',rzsj=getdate() where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '启用人员档案', '修改', 'shipSRM_ryda')
   res_util(res, {}, true, "启用成功")
})

//查询部门档案列表
router.post('/bmlb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_bmda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询部门档案列表通过id
router.post('/query_bm_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_bmda where id='${req.body.id}' order by create_time desc`)
   res_util(res, {formdata:data[0]}, true, "查询成功")
})

//新增部门档案
router.post('/add_bm', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_bmda where bmbh='${formdata.bmbh.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，工号已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_bmda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增部门档案', '新增', 'shipSRM_bmda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新部门档案
router.post('/update_bm', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from shipSRM_bmda where 
   bmbh='${formdata.bmbh.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，部门编号已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_bmda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新部门档案', '更新', 'shipSRM_bmda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})




//导入客户信息 excel

router.post('/khda_import', async function (req, res) {
   var data=req.body
   var db=new Msdb()
  for(var i=0;i<data.length;i++){

      
  await db.update(`insert into shipSRM_khda(id,create_time,create_by,mc,lxr,dh,dz,bm)
    values(NEXT VALUE FOR SEQ_ID,getdate(),'admin','${data[i].mc}','${data[i].lxr}','${data[i].dh}','${data[i].dz}','${data[i].bm}')`)
   
  }


   res_util(res, {}, true, "导入完成")
})

//查询客户档案列表
router.post('/khdalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_khda order by bm desc`)
   res_util(res, rtdata, true, "查询成功")
})


//查询客户档案列表通过id
router.post('/query_khda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_khda where id='${req.body.id}' order by bm desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增客户档案
router.post('/add_khda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_khda where bm='${formdata.bm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，客户编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_khda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增客户档案', '新增', 'shipSRM_khda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新客户档案
router.post('/update_khda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_khda where 
   bm='${formdata.bm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，客户编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_khda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新客户档案', '更新', 'shipSRM_khda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除客户档案
router.post('/delete_khda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_khda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除客户档案', '删除', 'shipSRM_khda')
   res_util(res, {}, true, "删除成功")
})

//查询车间档案列表
router.post('/cjdalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_cjda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询车间档案列表通过id
router.post('/query_cjda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_cjda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增车间档案
router.post('/add_cjda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_cjda where cjbm='${formdata.cjbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，车间编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_cjda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增车间档案', '新增', 'shipSRM_cjda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新车间档案
router.post('/update_cjda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_cjda where 
   cjbm='${formdata.cjbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，车间编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_cjda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新车间档案', '更新', 'shipSRM_cjda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除车间档案
router.post('/delete_cjda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_cjda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除车间档案', '删除', 'shipSRM_cjda')
   res_util(res, {}, true, "删除成功")
})


//查询物料分类树
router.post('/wlfllb', async function (req, res) {
   var rtdata = await usequery(`select *,sjflid parentid,flmc name,flbm bm from shipSRM_wlfl order by flbm desc`)
   res_util(res, rtdata, true, "查询成功")
})


//查询物料分类列表通过id
router.post('/query_wlfl_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_wlfl where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增物料分类
router.post('/add_wlfl', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_wlfl where flbm='${formdata.flbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，物料分类编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_wlfl")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增物料分类', '新增', 'shipSRM_wlfl')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//新增一级分类
router.post('/add_wlfl_yjfl', async function (req, res) {
   let { flbm,flmc, storage } = req.body
   var db=new Msdb()
   var ifbm = await checkbysql(` select top 1 * from shipSRM_wlfl where flbm='${flbm}'`)
   if (ifbm) {
      res_util(res, { }, false, "新增失败，物料分类编码已存在")
      return
   }
   //不存在编码插入
   let id=await getxl_util(sql)
   let sql=`insert into shipSRM_wlfl_new(id,create_time,create_by,flbm,flmc,sjflid) values('${id}',getdate(),'${storage.ygxm}','${flmc}','${flbm}','000')`
   await saveczlog_util(sql, storage, '', '新增物料分类', '新增', 'shipSRM_wlfl')
   res_util(res, {}, true, "新增成功")
})

//更新物料分类
router.post('/update_wlfl', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_wlfl where 
   flbm='${formdata.flbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，物料分类编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_wlfl")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新物料分类', '更新', 'shipSRM_wlfl')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除物料分类
router.post('/delete_wlfl', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_wlfl where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除物料分类', '删除', 'shipSRM_wlfl')
   res_util(res, {}, true, "删除成功")
})

//查询产线档案列表
router.post('/cxdalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_cxda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询产线档案列表通过id
router.post('/query_cxda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_cxda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增产线档案
router.post('/add_cxda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_cxda where cxbm='${formdata.cxbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，产线编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_cxda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增产线档案', '新增', 'shipSRM_cxda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新产线档案
router.post('/update_cxda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_cxda where 
   cxbm='${formdata.cxbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，产线编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_cxda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新产线档案', '更新', 'shipSRM_cxda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除产线档案
router.post('/delete_cxda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_cxda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除产线档案', '删除', 'shipSRM_cxda')
   res_util(res, {}, true, "删除成功")
})


//查询仓库档案列表
router.post('/ckdalb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_ckda order by ckbm desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询仓库档案列表通过id
router.post('/query_ckda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_ckda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增仓库档案
router.post('/add_ckda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_ckda where ckbm='${formdata.ckbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，仓库档案已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_ckda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增仓库档案', '新增', 'shipSRM_ckda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新仓库档案
router.post('/update_ckda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_ckda where 
   ckbm='${formdata.ckbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，物料编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_ckda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新仓库档案', '更新', 'shipSRM_ckda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除仓库档案
router.post('/delete_ckda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_ckda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除仓库档案', '新增', 'shipSRM_ckda')
   res_util(res, {}, true, "删除成功")
})



//查询角色数据
router.post('/queryJsDataSource',async function(req,res){
      var rtdata=await usequery(`select * from shipSRM_js`);
      res_util(res, rtdata, true, "查询成功")
   });

//查询物料数据
router.post('/queryWlmcDataSource',async function(req,res){
   var rtdata=await usequery(`select * from shipSRM_wlda`);
   res_util(res, rtdata, true, "查询成功")
});

//查询仓库数据
router.post('/queryCkDataSource',async function(req,res){
   var rtdata=await usequery(`select *  from shipSRM_ckda`);
   res_util(res, rtdata, true, "查询成功")
});

//查询产品档案列表
router.post('/cpdalb', async function (req, res) {
   let {storage } = req.body
   var rtdata = await usequery(`select * from shipSRM_cpda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询产品档案列表通过id
router.post('/query_cpda_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_cpda where id='${req.body.data.id}' order by create_time desc`)
   var mxdata = await usequery(`select b.* from shipSRM_wlqd a,shipSRM_wlqd_mx b where a.id=b.mainid and a.cpbm='${data[0].wlbm}'`)
   var gylxmxdata = await usequery(`select b.* from shipSRM_gylx a,shipSRM_gylx_gymx b where a.id=b.mainid and a.cpbm='${data[0].wlbm}' order by b.sx asc`)
   res_util(res, { formdata: data[0],mxdata,gylxmxdata}, true, "查询成功")
})

//新增产品档案
router.post('/add_cpda', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   var insrtobj = await insert_table(formdata, "shipSRM_cpda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增产品档案', '新增', 'shipSRM_cpda')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_cpda_tzbgmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增产品档案明细', '新增', 'shipSRM_cpda_tzbgmx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_cpda_tzbgmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增产品档案成功`)
})

//更新产品档案
router.post('/update_cpda', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_cpda")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改产品档案', '修改', 'shipSRM_cpda')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_cpda_tzbgmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_cpda_tzbgmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_cpda_tzbgmx")
      }
   }
   
   //删除delidarr
   await db.update(`delete from shipSRM_cpda_tzbgmx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_cpda_tzbgmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新产品档案成功")
})

//  删除产品档案
router.post('/delete_cpda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_cpda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_cpda_tzbgmx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除产品档案', '删除', 'shipSRM_cpda')
   res_util(res, {}, true, "删除成功")
})


module.exports = router; 